

/*
数据库连接信息
用户名/密码@IP地址/数据库名称  system  root   192.168.3.138  ORCL
*/

---------------------日志记录表---------------------
CREATE TABLE logrecord (
                           id NUMBER(20) NOT NULL,
                           requestid VARCHAR2(64) NOT NULL,
                           url VARCHAR2(4000),
                           method VARCHAR2(10),
                           path VARCHAR2(1000),
                           remoteip VARCHAR2(50),
                           sessionid VARCHAR2(100),
                           duration NUMBER,
                           headers CLOB,
                           requestparams CLOB,
                           requestbody CLOB,
                           responsebody CLOB,
                           isupload NUMBER(1) DEFAULT 0,
                           isdownload NUMBER(1) DEFAULT 0,
                           contenttype VARCHAR2(255),
                           status NUMBER(3),
                           sessionattributes CLOB,
                           replaystatus VARCHAR2(1),
                           createtime TIMESTAMP(6) DEFAULT SYSTIMESTAMP,
                           reqtime           TIMESTAMP(6)

                           CONSTRAINT pk_logrecord PRIMARY KEY (id),
                           CONSTRAINT uk_logrecord_requestid UNIQUE (requestid)
);

COMMENT ON TABLE logrecord IS '日志记录表';
COMMENT ON COLUMN logrecord.id IS '自增主键ID';
COMMENT ON COLUMN logrecord.requestid IS '请求ID';
COMMENT ON COLUMN logrecord.url IS '完整URL(包含查询参数)';
COMMENT ON COLUMN logrecord.method IS '请求方法';
COMMENT ON COLUMN logrecord.path IS '请求路径';
COMMENT ON COLUMN logrecord.remoteip IS '远程IP地址';
COMMENT ON COLUMN logrecord.sessionid IS '会话ID';
COMMENT ON COLUMN logrecord.duration IS '请求持续时间';
COMMENT ON COLUMN logrecord.headers IS '请求头(JSON格式)';
COMMENT ON COLUMN logrecord.requestparams IS '请求参数(JSON格式)';
COMMENT ON COLUMN logrecord.requestbody IS '请求体内容';
COMMENT ON COLUMN logrecord.responsebody IS '响应体内容';
COMMENT ON COLUMN logrecord.isupload IS '是否上传(1:是,0:否)';
COMMENT ON COLUMN logrecord.isdownload IS '是否下载(1:是,0:否)';
COMMENT ON COLUMN logrecord.contenttype IS '请求内容类型';
COMMENT ON COLUMN logrecord.status IS '响应状态码';
COMMENT ON COLUMN logrecord.sessionattributes IS '会话属性(JSON格式)';
COMMENT ON COLUMN logrecord.replaystatus IS '回放状态(0-待回放，1-回放完成)';
COMMENT ON COLUMN logrecord.createtime IS '记录创建时间';
comment on column LOGRECORD.reqtime IS '请求时间';

CREATE SEQUENCE seq_logrecord_id START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trg_logrecord_id
    BEFORE INSERT ON logrecord
    FOR EACH ROW
BEGIN
    SELECT seq_logrecord_id.NEXTVAL INTO :NEW.id FROM dual;
END;
/

---------------------流量回放日志表---------------------
CREATE TABLE replay_log (
                            id NUMBER(20) NOT NULL,
                            requestid VARCHAR2(64) NOT NULL,
                            url VARCHAR2(4000),
                            method VARCHAR2(10),
                            path VARCHAR2(1000),
                            remoteip VARCHAR2(50),
                            sessionid VARCHAR2(100),
                            duration NUMBER,
                            headers CLOB,
                            requestparams CLOB,
                            requestbody CLOB,
                            responsebody CLOB,
                            isupload NUMBER(1) DEFAULT 0,
                            isdownload NUMBER(1) DEFAULT 0,
                            contenttype VARCHAR2(255),
                            status NUMBER(3),
                            sessionattributes CLOB,
                            timestamp VARCHAR2(50),
                            compare_status VARCHAR2(1),
                            createtime TIMESTAMP(6) DEFAULT SYSTIMESTAMP,
                            replay_status VARCHAR2(1),
                            CONSTRAINT pk_replay_log PRIMARY KEY (id),
                            CONSTRAINT uk_replay_log_requestid UNIQUE (requestid)
);

COMMENT ON TABLE replay_log IS '流量回放日志表';
COMMENT ON COLUMN replay_log.id IS '自增主键ID';
COMMENT ON COLUMN replay_log.requestid IS '请求ID';
COMMENT ON COLUMN replay_log.url IS '完整URL(包含查询参数)';
COMMENT ON COLUMN replay_log.method IS '请求方法';
COMMENT ON COLUMN replay_log.path IS '请求路径';
COMMENT ON COLUMN replay_log.remoteip IS '远程IP地址';
COMMENT ON COLUMN replay_log.sessionid IS '会话ID';
COMMENT ON COLUMN replay_log.duration IS '请求持续时间';
COMMENT ON COLUMN replay_log.headers IS '请求头(JSON格式)';
COMMENT ON COLUMN replay_log.requestparams IS '请求参数(JSON格式)';
COMMENT ON COLUMN replay_log.requestbody IS '请求体内容';
COMMENT ON COLUMN replay_log.responsebody IS '响应体内容';
COMMENT ON COLUMN replay_log.isupload IS '是否上传(1:是,0:否)';
COMMENT ON COLUMN replay_log.isdownload IS '是否下载(1:是,0:否)';
COMMENT ON COLUMN replay_log.contenttype IS '请求内容类型';
COMMENT ON COLUMN replay_log.status IS '响应状态码';
COMMENT ON COLUMN replay_log.sessionattributes IS '会话属性(JSON格式)';
COMMENT ON COLUMN replay_log.timestamp IS '时间戳';
COMMENT ON COLUMN replay_log.compare_status IS '对比状态(0-待对比，1-对比完成)';
COMMENT ON COLUMN replay_log.createtime IS '记录创建时间';
COMMENT ON COLUMN replay_log.replay_status IS '对比状态(0-待对比，1-对比完成)';

CREATE SEQUENCE seq_replay_log_id START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trg_replay_log_id
    BEFORE INSERT ON replay_log
    FOR EACH ROW
BEGIN
    SELECT seq_replay_log_id.NEXTVAL INTO :NEW.id FROM dual;
END;
/



---------------------接口映射配置表---------------------
create table INTERFACE_MAPPING_CONFIG
(
    id             NUMBER not null,
    interface_no   VARCHAR2(100) not null,
    interface_name VARCHAR2(255),
    old_path       VARCHAR2(255),
    new_path       VARCHAR2(255),
    mapping_config CLOB,
    write_flag     VARCHAR2(1),
    valid_flag     VARCHAR2(1)
)
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-- Add comments to the table
comment on table INTERFACE_MAPPING_CONFIG
    is '接口映射配置表';
-- Add comments to the columns
comment on column INTERFACE_MAPPING_CONFIG.id
    is '主键';
comment on column INTERFACE_MAPPING_CONFIG.interface_no
    is '接口编号(接口唯一标识，如URL+Method)';
comment on column INTERFACE_MAPPING_CONFIG.interface_name
    is '接口名';
comment on column INTERFACE_MAPPING_CONFIG.old_path
    is '原接口路径';
comment on column INTERFACE_MAPPING_CONFIG.new_path
    is '新接口路径';
comment on column INTERFACE_MAPPING_CONFIG.mapping_config
    is '映射配置(JSON数组)';
comment on column INTERFACE_MAPPING_CONFIG.write_flag
    is '是否交易接口(0-否，1-是)';
comment on column INTERFACE_MAPPING_CONFIG.valid_flag
    is '是否生效(0-否，1-是)';
-- Create/Recreate primary, unique and foreign key constraints
alter table INTERFACE_MAPPING_CONFIG
    add constraint PK_INTERFACE_MAPPING_CONFIG primary key (ID);

CREATE SEQUENCE INTERFACE_MAPPING_CONFIG_SEQ START WITH 1  INCREMENT BY 1;
CREATE OR REPLACE TRIGGER INTERFACE_MAPPING_CONFIG_TRG
    BEFORE INSERT ON INTERFACE_MAPPING_CONFIG
    FOR EACH ROW
BEGIN
    SELECT INTERFACE_MAPPING_CONFIG_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

---------------------流量回放结果记录表---------------------
CREATE TABLE REPLAY_RESULT (
                               id NUMBER(20) NOT NULL,
                               replay_id NUMBER(20) NOT NULL,
                               request_id VARCHAR2(64) NOT NULL,
                               original_value CLOB,
                               replay_value CLOB,
                               diff_value VARCHAR2(4000),
                               diff_flag VARCHAR2(1),
                               replay_time TIMESTAMP(6) DEFAULT SYSTIMESTAMP,
                               CONSTRAINT pk_replay_result PRIMARY KEY (id)
);

COMMENT ON TABLE REPLAY_RESULT IS '流量回放结果记录表';
COMMENT ON COLUMN REPLAY_RESULT.id IS '任务回放结果ID';
COMMENT ON COLUMN REPLAY_RESULT.replay_id IS '流量回放日志表ID';
COMMENT ON COLUMN REPLAY_RESULT.request_id IS '请求ID';
COMMENT ON COLUMN REPLAY_RESULT.original_value IS '原始值';
COMMENT ON COLUMN REPLAY_RESULT.replay_value IS '回放值';
COMMENT ON COLUMN REPLAY_RESULT.diff_value IS '差异内容(存放不一致报文下标)';
COMMENT ON COLUMN REPLAY_RESULT.diff_flag IS '是否有差异(0-否，1-是)';
COMMENT ON COLUMN REPLAY_RESULT.replay_time IS '回放时间';


CREATE SEQUENCE seq_replay_result_id START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trg_replay_result_id
    BEFORE INSERT ON REPLAY_RESULT
    FOR EACH ROW
BEGIN
    SELECT seq_replay_result_id.NEXTVAL INTO :NEW.id FROM dual;
END;
/

-----------------------------FILE_DATA_RECORD--------------------
-- 适配oceanbase数据库
CREATE TABLE FILE_DATA_RECORD
(
    id              BIGINT NOT NULL AUTO_INCREMENT,
    record_type     VARCHAR(10) NOT NULL,
    interface_url   VARCHAR(200) NOT NULL,
    request_id      VARCHAR(50) NOT NULL,
    md5_code        VARCHAR(32) NOT NULL,
    is_match        TINYINT DEFAULT 0,
    original_data   TEXT,
    process_time    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    process_status  TINYINT DEFAULT 1,
    error_msg       VARCHAR(1000),
    filename        VARCHAR(20),
    mapping_field1  VARCHAR(200),
    mapping_field2  VARCHAR(200),
    mapping_field3  VARCHAR(200),
    mapping_field4  VARCHAR(200),
    mapping_field5  VARCHAR(200),
    mapping_field6  VARCHAR(200),
    mapping_field7  VARCHAR(200),
    mapping_field8  VARCHAR(200),
    mapping_field9  VARCHAR(200),
    mapping_field10 VARCHAR(200),
    mapping_field11 VARCHAR(200),
    mapping_field12 VARCHAR(200),
    mapping_field13 VARCHAR(200),
    mapping_field14 VARCHAR(200),
    mapping_field15 VARCHAR(200),
    mapping_field16 VARCHAR(200),
    mapping_field17 VARCHAR(200),
    mapping_field18 VARCHAR(200),
    mapping_field19 VARCHAR(200),
    mapping_field20 VARCHAR(200),
    mapping_field21 VARCHAR(200),
    mapping_field22 VARCHAR(200),
    mapping_field23 VARCHAR(200),
    mapping_field24 VARCHAR(200),
    mapping_field25 VARCHAR(200),
    mapping_field26 VARCHAR(200),
    mapping_field27 VARCHAR(200),
    mapping_field28 VARCHAR(200),
    mapping_field29 VARCHAR(200),
    mapping_field30 VARCHAR(200),
    mapping_field31 VARCHAR(200),
    mapping_field32 VARCHAR(200),
    mapping_field33 VARCHAR(200),
    mapping_field34 VARCHAR(200),
    mapping_field35 VARCHAR(200),
    mapping_field36 VARCHAR(200),
    mapping_field37 VARCHAR(200),
    mapping_field38 VARCHAR(200),
    mapping_field39 VARCHAR(200),
    mapping_field40 VARCHAR(200),
    mapping_field41 VARCHAR(200),
    mapping_field42 VARCHAR(200),
    mapping_field43 VARCHAR(200),
    mapping_field44 VARCHAR(200),
    mapping_field45 VARCHAR(200),
    mapping_field46 VARCHAR(200),
    mapping_field47 VARCHAR(200),
    mapping_field48 VARCHAR(200),
    mapping_field49 VARCHAR(200),
    mapping_field50 VARCHAR(200),
    PRIMARY KEY (id)
) DEFAULT CHARSET = utf8mb4;

-- Add comments to the table
COMMENT ON TABLE FILE_DATA_RECORD IS '文件数据记录表';
-- Add comments to the columns
COMMENT ON COLUMN FILE_DATA_RECORD.id IS '主键ID';
COMMENT ON COLUMN FILE_DATA_RECORD.record_type IS '记录类型(新/老)';
COMMENT ON COLUMN FILE_DATA_RECORD.interface_url IS '接口URL';
COMMENT ON COLUMN FILE_DATA_RECORD.request_id IS '请求ID';
COMMENT ON COLUMN FILE_DATA_RECORD.md5_code IS '文件MD5码';
COMMENT ON COLUMN FILE_DATA_RECORD.is_match IS '是否匹配(0-否 1-是)';
COMMENT ON COLUMN FILE_DATA_RECORD.original_data IS '原始数据';
COMMENT ON COLUMN FILE_DATA_RECORD.process_time IS '处理时间';
COMMENT ON COLUMN FILE_DATA_RECORD.process_status IS '处理状态(0-失败 1-成功)';
COMMENT ON COLUMN FILE_DATA_RECORD.error_msg IS '错误信息';
COMMENT ON COLUMN FILE_DATA_RECORD.filename IS '文件名';

---------------------系统配置表---------------------
CREATE TABLE sys_config (
                            id NUMBER(20) NOT NULL,
                            type VARCHAR2(64) NOT NULL,
                            value VARCHAR2(512) NOT NULL,
                            remark VARCHAR2(512),
                            CONSTRAINT pk_sys_config PRIMARY KEY (id)
);

COMMENT ON TABLE sys_config IS '系统配置表';
COMMENT ON COLUMN sys_config.id IS 'ID';
COMMENT ON COLUMN sys_config.type IS '类型';
COMMENT ON COLUMN sys_config.value IS '值';
COMMENT ON COLUMN sys_config.remark IS '备注';

CREATE SEQUENCE seq_sys_config_id START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trg_sys_config_id
    BEFORE INSERT ON sys_config
    FOR EACH ROW
BEGIN
    SELECT seq_sys_config_id.NEXTVAL INTO :NEW.id FROM dual;
END;
/

-- 表注释
COMMENT ON TABLE FILE_CONFIG IS '文件配置表';

-- 列注释
COMMENT ON COLUMN FILE_CONFIG.id IS '主键ID(自增)';
COMMENT ON COLUMN FILE_CONFIG.interface_adrs IS '接口标识';
COMMENT ON COLUMN FILE_CONFIG.file_type IS '文件类型';
COMMENT ON COLUMN FILE_CONFIG.file_field IS '文件字段';
COMMENT ON COLUMN FILE_CONFIG.mapping_field IS '映射字段';
COMMENT ON COLUMN FILE_CONFIG.is_ignore IS '是否忽略(0-否 1-是)';
COMMENT ON COLUMN FILE_CONFIG.has_value_mapping IS '是否存在值映射(0-否 1-是)';
COMMENT ON COLUMN FILE_CONFIG.read_order IS '读取顺序(数字越小优先级越高)';
COMMENT ON COLUMN FILE_CONFIG.value_mapping_json IS '值映射配置JSON';
COMMENT ON COLUMN FILE_CONFIG.filename IS '文件名';

----------------------FILE_COMPARE_RECORD----------------------
CREATE TABLE FILE_COMPARE_RECORD (
                                     id             BIGINT NOT NULL AUTO_INCREMENT,
                                     interface_url  VARCHAR(200) NOT NULL,
                                     request_id     VARCHAR(50) NOT NULL,
                                     compare_result TEXT NOT NULL,
                                     compare_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                     compare_status TINYINT DEFAULT 1,
                                     PRIMARY KEY (id)
) DEFAULT CHARSET = utf8mb4;

-- 表注释
COMMENT ON TABLE FILE_COMPARE_RECORD IS '文件对比记录表';

-- 列注释
COMMENT ON COLUMN FILE_COMPARE_RECORD.id IS '主键ID';
COMMENT ON COLUMN FILE_COMPARE_RECORD.interface_url IS '接口URL';
COMMENT ON COLUMN FILE_COMPARE_RECORD.request_id IS '请求ID';
COMMENT ON COLUMN FILE_COMPARE_RECORD.compare_result IS '对比结果(0-无差异 1-有差异)';
COMMENT ON COLUMN FILE_COMPARE_RECORD.compare_time IS '对比时间';
COMMENT ON COLUMN FILE_COMPARE_RECORD.compare_status IS '对比状态(0-完成 1-失败)';

---------------------------------------FILE_CONFIG---------------------------------------
CREATE TABLE FILE_CONFIG
(
    id                 BIGINT NOT NULL AUTO_INCREMENT,
    interface_adrs     VARCHAR(100) NOT NULL,
    file_type          VARCHAR(50) NOT NULL,
    file_field         VARCHAR(100) NOT NULL,
    mapping_field      VARCHAR(100) NOT NULL,
    is_ignore          TINYINT DEFAULT 0 NOT NULL,
    has_value_mapping  TINYINT DEFAULT 0 NOT NULL,
    read_order         INT DEFAULT 999 NOT NULL,
    value_mapping_json TEXT,
    filename           VARCHAR(20),
    PRIMARY KEY (id)
) DEFAULT CHARSET = utf8mb4;


---------------------------------------GPS_CONFIG_PARALLELL---------------------------------------
---------------------------------------配置表，用于存储系统配置信息----------------------------------
CREATE TABLE GPS_CONFIG_PARALLELL (
                                      TABLE_NAME  VARCHAR2(120) NOT NULL,
                                      FIELD       VARCHAR2(120) NOT NULL,
                                      SELECTSQL   VARCHAR2(3000)
);
-- 表注释
COMMENT ON TABLE GPS_CONFIG_PARALLELL IS '配置表，用于存储系统配置信息';

-- 列注释
COMMENT ON COLUMN GPS_CONFIG_PARALLELL.TABLE_NAME IS '配置对应的数据库表名';
COMMENT ON COLUMN GPS_CONFIG_PARALLELL.FIELD IS '配置对应的字段名';
COMMENT ON COLUMN GPS_CONFIG_PARALLELL.SELECTSQL IS '用于查询配置数据的SQL语句';


---------------------------------------GPS_CONFIG_APIID_POLICYNO---------------------------------------
---------------------------------------接口与保单号策略配置表--------------------------------------------
CREATE TABLE "GPS_CONFIG_APIID_POLICYNO" (
                                             "API_ID" VARCHAR2(120) NOT NULL,
                                             "API_TYPE" NUMBER(38) NOT NULL,
                                             "PARAM" VARCHAR2(120),
                                             "DIGIT" VARCHAR2(120),
                                             "SUB_TYPE" VARCHAR2(120),
                                             "SUB_RULE" VARCHAR2(120),
                                             "ZX_SQL" VARCHAR2(2000),
                                             "API_PREFIX" VARCHAR2(2),
                                             "TABLE_NAME" VARCHAR2(30),
                                             "BAK_WHERE_SQL" VARCHAR2(200),
                                             "UTIME_TYPE" VARCHAR2(30),
                                             "UPTIME_TYPE" VARCHAR2(30),
                                             "TABLE_PKS" VARCHAR2(200),
                                             "COMP_CLOS" VARCHAR2(2000),
                                             "UTIME_NAME" VARCHAR2(20),
                                             "UPDATE_TIME_NAME" VARCHAR2(20),
                                             "BRANCHID_FLAG" VARCHAR2(2),
                                             "BUSS_NO" VARCHAR2(200),
                                             "UTIME" VARCHAR2(50) DEFAULT TO_CHAR(sysdate, 'yyyy-mm-dd HH24:mi:ss')
);

-- 表注释
COMMENT ON TABLE GPS_CONFIG_APIID_POLICYNO IS '接口与保单号策略配置表，用于存储接口参数、规则及SQL逻辑等信息';

-- 列注释
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.API_ID IS '接口唯一标识';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.API_TYPE IS '接口类型编号';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.PARAM IS '参数名称';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.DIGIT IS '数字相关配置';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.SUB_TYPE IS '截取规则(0截取，1去除)';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.SUB_RULE IS '截取规则说明';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.ZX_SQL IS '特殊规则执行SQL';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.API_PREFIX IS '接口编号前缀';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.TABLE_NAME IS '对应的数据库表名';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.BAK_WHERE_SQL IS '备份时使用的WHERE条件';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.UTIME_TYPE IS '时间类型字段（UTIME）格式定义';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.UPTIME_TYPE IS '更新时间字段格式定义';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.TABLE_PKS IS '表主键字段列表';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.COMP_CLOS IS '需要对比的字段';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.UTIME_NAME IS '开始时间字段名称';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.UPDATE_TIME_NAME IS '更新结束时间字段名称';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.BRANCHID_FLAG IS '是否存在branchid字段标识（0否 1是）';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.BUSS_NO IS '业务号字段';
COMMENT ON COLUMN GPS_CONFIG_APIID_POLICYNO.UTIME IS '记录更新时间（默认当前时间）';


CREATE INDEX "SYSTEM"."IDX_CP_CONFIG_APIID_API_ID" ON "SYSTEM"."GPS_CONFIG_APIID_POLICYNO" ("API_ID") GLOBAL;



---------------------------------------GPS_DEAL_COMPARE_DATA_LOG---------------------------------------
---------------------------------------数据对比日志表----------------------------------------------------
CREATE TABLE "GPS_DEAL_COMPARE_DATA_LOG" (
                                             "ID" NUMBER(22) NOT NULL,
                                             "LOG_ID" NUMBER(22),
                                             "COMPARE_ID" NUMBER(22),
                                             "API_ID" VARCHAR2(20),
                                             "POLICYNO" VARCHAR2(20),
                                             "FLAG" VARCHAR2(2),
                                             "NEW_DATA_NUM" VARCHAR2(5),
                                             "OLD_DATA_NUM" VARCHAR2(5),
                                             "TABLE_NAME" VARCHAR2(30),
                                             "START_TIME" VARCHAR2(20),
                                             "END_TIME" VARCHAR2(20),
                                             CONSTRAINT PK_GPS_DEAL_COMPARE_DATA_LOG PRIMARY KEY (ID)
);

-- 表注释
COMMENT ON TABLE GPS_DEAL_COMPARE_DATA_LOG IS '数据对比日志表，记录接口与保单号的数据差异';

-- 字段注释
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.ID IS '主键ID（自增）';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.LOG_ID IS '关联的日志ID';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.COMPARE_ID IS '关联的对比任务ID';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.API_ID IS '接口唯一标识';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.POLICYNO IS '保单号';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.FLAG IS '状态标识（如：0-正常 1-异常）';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.NEW_DATA_NUM IS '新系统数据条数';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.OLD_DATA_NUM IS '老系统数据条数';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.TABLE_NAME IS '对应的数据库表名';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.START_TIME IS '开始时间';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA_LOG.END_TIME IS '结束时间';

-- 创建序列实现 ID 自增
CREATE SEQUENCE SEQ_GPS_DEAL_COMPARE_DATA_LOG
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE;

-- 创建触发器，在插入时自动赋值 ID
CREATE OR REPLACE TRIGGER TRG_GPS_DEAL_COMPARE_DATA_LOG
    BEFORE INSERT ON GPS_DEAL_COMPARE_DATA_LOG
    FOR EACH ROW
BEGIN
    SELECT SEQ_GPS_DEAL_COMPARE_DATA_LOG.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/


---------------------------------------GPS_DEAL_COMPARE_DATA---------------------------------------
-------------------------------------------数据对比记录表-------------------------------------------
CREATE TABLE "GPS_DEAL_COMPARE_DATA" (
                                         "ID" NUMBER(22) NOT NULL,
                                         "LOG_ID" NUMBER(22),
                                         "COMPARE_ID" VARCHAR2(120),
                                         "API_ID" VARCHAR2(20),
                                         "POLICYNO" VARCHAR2(20),
                                         "NEW_DATA" CLOB,
                                         "OLD_DATA" CLOB,
                                         "TABLE_NAME" VARCHAR2(30),
                                         "SOURCE" VARCHAR2(2),
                                         "CREATE_TIME" DATE,
                                         "DIFFER" CLOB,
                                         "OB_MORE" CLOB,
                                         "OB_LESS" CLOB,
                                         "RESULT_TYPE" VARCHAR2(40),
                                         CONSTRAINT PK_GPS_DEAL_COMPARE_DATA PRIMARY KEY (ID)
);

-- 表注释
COMMENT ON TABLE GPS_DEAL_COMPARE_DATA IS '数据对比记录表，用于存储接口与保单号的新老系统数据差异';

-- 字段注释
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.ID IS '主键ID（自增）';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.LOG_ID IS '关联的日志ID';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.COMPARE_ID IS '对比任务唯一标识';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.API_ID IS '接口唯一标识';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.POLICYNO IS '保单号';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.NEW_DATA IS '新系统返回的完整数据';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.OLD_DATA IS '老系统返回的完整数据';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.TABLE_NAME IS '对应的数据库表名';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.SOURCE IS '数据来源（如：0-新系统 1-老系统）';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.CREATE_TIME IS '记录创建时间';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.DIFFER IS '差异内容描述（JSON格式）';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.OB_MORE IS '新系统多出的数据';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.OB_LESS IS '新系统缺失的数据';
COMMENT ON COLUMN GPS_DEAL_COMPARE_DATA.RESULT_TYPE IS '对比结果类型（如：完全一致、部分一致、不一致）';

-- 创建序列实现 ID 自增
CREATE SEQUENCE SEQ_GPS_DEAL_COMPARE_DATA
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE;

-- 创建触发器，在插入时自动赋值 ID
CREATE OR REPLACE TRIGGER TRG_GPS_DEAL_COMPARE_DATA
    BEFORE INSERT ON GPS_DEAL_COMPARE_DATA
    FOR EACH ROW
BEGIN
    SELECT SEQ_GPS_DEAL_COMPARE_DATA.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

---------------------------------------POLI_BRANCHID---------------------------------------
-------------------------------------保单与机构号映射表--------------------------------------
CREATE TABLE "POLI_BRANCHID" (
                                 "POLICYNO" VARCHAR2(15),
                                 "BRANCHID" VARCHAR2(14),
                                 CONSTRAINT "PK_POLI_BRANCHID" PRIMARY KEY ("POLICYNO", "BRANCHID")
)
    partition by hash(policyno)
(partition P01_S01,
 partition P01_S02,
 partition P01_S03,
 partition P01_S04,
 partition P01_S05,
 partition P01_S06,
 partition P01_S07,
 partition P01_S08,
 partition P01_S09,
 partition P01_S10,
 partition P01_S11,
 partition P01_S12,
 partition P01_S13,
 partition P01_S14,
 partition P01_S15,
 partition P01_S16,
 partition P01_S17,
 partition P01_S18,
 partition P01_S19,
 partition P01_S20,
 partition P01_S21,
 partition P01_S22,
 partition P01_S23,
 partition P01_S24,
 partition P01_S25,
 partition P01_S26,
 partition P01_S27,
 partition P01_S28,
 partition P01_S29,
 partition P01_S30,
 partition P01_S31,
 partition P01_S32);

-- 表注释
COMMENT ON TABLE POLI_BRANCHID IS '保单与机构号映射表，用于记录保单号对应的分支机构编号';

-- 字段注释
COMMENT ON COLUMN POLI_BRANCHID.POLICYNO IS '保单号';
COMMENT ON COLUMN POLI_BRANCHID.BRANCHID IS '分支机构编号';

CREATE INDEX "SYSTEM"."IDX_POLI_BRANCHID_01" on "SYSTEM"."POLI_BRANCHID" (
                                                                          "POLICYNO"
    ) LOCAL ;



---------------------------------------GPOLI_BRANCHID---------------------------------------
----------------------------------保单号与分支机构编号映射表-----------------------------------
CREATE TABLE "GPOLI_BRANCHID" (
                                  "GPOLICYNO" VARCHAR2(15) NOT NULL,
                                  "BRANCHID" VARCHAR2(14) NOT NULL,
                                  CONSTRAINT "PK_GPOLI_BRANCHID" PRIMARY KEY ("GPOLICYNO", "BRANCHID")
)
    PARTITION BY HASH (GPOLICYNO)
(
    PARTITION P01_S01,
    PARTITION P01_S02,
    PARTITION P01_S03,
    PARTITION P01_S04,
    PARTITION P01_S05,
    PARTITION P01_S06,
    PARTITION P01_S07,
    PARTITION P01_S08,
    PARTITION P01_S09,
    PARTITION P01_S10,
    PARTITION P01_S11,
    PARTITION P01_S12,
    PARTITION P01_S13,
    PARTITION P01_S14,
    PARTITION P01_S15,
    PARTITION P01_S16,
    PARTITION P01_S17,
    PARTITION P01_S18,
    PARTITION P01_S19,
    PARTITION P01_S20,
    PARTITION P01_S21,
    PARTITION P01_S22,
    PARTITION P01_S23,
    PARTITION P01_S24,
    PARTITION P01_S25,
    PARTITION P01_S26,
    PARTITION P01_S27,
    PARTITION P01_S28,
    PARTITION P01_S29,
    PARTITION P01_S30,
    PARTITION P01_S31,
    PARTITION P01_S32
);

-- 表注释
COMMENT ON TABLE GPOLI_BRANCHID IS '保单号与分支机构编号映射表';

-- 字段注释
COMMENT ON COLUMN GPOLI_BRANCHID.GPOLICYNO IS '保单号（G开头标识新系统）';
COMMENT ON COLUMN GPOLI_BRANCHID.BRANCHID IS '分支机构编号';

-- 创建本地索引（LOCAL），适合分区表查询优化
CREATE INDEX "SYSTEM"."IDX_GPOLI_BRANCHID_01" ON "SYSTEM"."GPOLI_BRANCHID" ("GPOLICYNO") LOCAL;

-- 可为字段加注释（间接说明索引作用）
COMMENT ON COLUMN GPOLI_BRANCHID.GPOLICYNO IS '保单号（G开头标识新系统），该字段为本地索引字段';



---------------------------------------GRPLIST---------------------------------------
------------------------------------团体保单明细表-------------------------------------
CREATE TABLE "GRPLIST" (
                           "GRPCODE" CHAR(12) DEFAULT ' ' NOT NULL,
                           "GAPPNO" CHAR(15) DEFAULT ' ' NOT NULL,
                           "GPOLICYNO" CHAR(15) DEFAULT ' ' NOT NULL,
                           "APPNO" CHAR(15) DEFAULT ' ' NOT NULL,
                           "WORKNO" CHAR(15),
                           "POLICYNO" CHAR(15) DEFAULT ' ' NOT NULL,
                           "PID" CHAR(18) DEFAULT ' ' NOT NULL,
                           "RECDATE" DATE NOT NULL,
                           "OPERNO" CHAR(8) DEFAULT ' '  NOT NULL,
                           "VERNO" CHAR(8) DEFAULT ' ' NOT NULL,
                           "VERDATE" DATE,
                           "MOVEDATE" DATE,
                           "TAC" CHAR(8) DEFAULT ' ' NOT NULL,
                           "UTIME" CHAR(16) DEFAULT ' ' NOT NULL,
                           "MBEGDATE" DATE,
                           "EMPNO" CHAR(8),
                           "GROUPS" CHAR(2),
                           "COMPID" CHAR(20) NOT NULL,
                           "UPDATETIME" CHAR(20),
                           "BRANCHID" CHAR(14) NOT NULL,
                           CONSTRAINT "PK_GRPLIST" PRIMARY KEY ("BRANCHID", "GAPPNO", "APPNO")
)
    partition by list("BRANCHID")
(partition P7 values  ('00000000000005','00000000000023','00000000000027','00000000000028','00000000000031'),
 partition P6 values  ('00000000000006','00000000000010','00000000000015','00000000000021','00000000000030'),
 partition P3 values  ('00000000000013','00000000000018','00000000000024','00000000002325','12045192433901'),
 partition P1 values  ('00000000000007','00000000000011','00000000000025','12977508116706'),
 partition P4 values  ('00000000000008','00000000000020','00000000000029','00000000002326'),
 partition P5 values  ('00000000000014','00000000000016','00000000000017','00000000000026'),
 partition P2 values  ('00000000000004','00000000000019','00000000000022'),
 partition P0 values  ('00000000000009','00000000000032','12045138254372'),
 partition P8 values  (DEFAULT));

COMMENT ON TABLE GRPLIST IS '团体保单明细表，用于存储保单相关的投保人、被保险人及机构信息';

COMMENT ON COLUMN GRPLIST.GRPCODE IS '团体保单代码';
COMMENT ON COLUMN GRPLIST.GAPPNO IS '团体投保单号';
COMMENT ON COLUMN GRPLIST.GPOLICYNO IS '新系统保单号（G开头标识）';
COMMENT ON COLUMN GRPLIST.APPNO IS '老系统保单号';
COMMENT ON COLUMN GRPLIST.WORKNO IS '工作编号';
COMMENT ON COLUMN GRPLIST.POLICYNO IS '保单号';
COMMENT ON COLUMN GRPLIST.PID IS '身份证号码';
COMMENT ON COLUMN GRPLIST.RECDATE IS '记录日期';
COMMENT ON COLUMN GRPLIST.OPERNO IS '操作员编号';
COMMENT ON COLUMN GRPLIST.VERNO IS '版本号';
COMMENT ON COLUMN GRPLIST.VERDATE IS '版本日期';
COMMENT ON COLUMN GRPLIST.MOVEDATE IS '迁移日期';
COMMENT ON COLUMN GRPLIST.TAC IS '交易码';
COMMENT ON COLUMN GRPLIST.UTIME IS '更新时间（格式：YYYYMMDDHH24MISS）';
COMMENT ON COLUMN GRPLIST.MBEGDATE IS '保障起始日期';
COMMENT ON COLUMN GRPLIST.EMPNO IS '员工编号';
COMMENT ON COLUMN GRPLIST.GROUPS IS '组别标识';
COMMENT ON COLUMN GRPLIST.COMPID IS '公司代码';
COMMENT ON COLUMN GRPLIST.UPDATETIME IS '最后更新时间（格式：YYYYMMDDHH24MISSSS）';
COMMENT ON COLUMN GRPLIST.BRANCHID IS '分支机构编号';


CREATE INDEX "SYSTEM"."GPOLICYNO_MOVEDATE_IDX01" on "SYSTEM"."GRPLIST" (
                                                                        "GPOLICYNO",
                                                                        "MOVEDATE",
                                                                        "POLICYNO"
    ) LOCAL ;

CREATE INDEX "SYSTEM"."IDX_GRPLIST_01" on "SYSTEM"."GRPLIST" (
                                                              "POLICYNO"
    ) LOCAL ;

CREATE INDEX "SYSTEM"."IDX_GRPLIST_02" on "SYSTEM"."GRPLIST" (
                                                              "PID"
    ) LOCAL ;

CREATE INDEX "SYSTEM"."IDX_GRPLIST_03" on "SYSTEM"."GRPLIST" (
                                                              "GRPCODE",
                                                              "APPNO"
    ) LOCAL ;

CREATE INDEX "SYSTEM"."IDX_GRPLIST_04" on "SYSTEM"."GRPLIST" (
                                                              "GAPPNO"
    ) LOCAL ;

CREATE INDEX "SYSTEM"."IDX_GRPLIST_05" on "SYSTEM"."GRPLIST" (
                                                              "APPNO"
    ) LOCAL ;

CREATE INDEX "SYSTEM"."IDX_GRPLIST_06" on "SYSTEM"."GRPLIST" (
                                                              "GPOLICYNO"
    ) LOCAL ;

CREATE INDEX "SYSTEM"."IDX_GRPLIST_07" on "SYSTEM"."GRPLIST" (
                                                              "GAPPNO",
                                                              "TAC"
    ) LOCAL ;

CREATE INDEX "SYSTEM"."IDX_GRPLIST_08" on "SYSTEM"."GRPLIST" (
                                                              "GPOLICYNO",
                                                              "BRANCHID",
                                                              "POLICYNO"
    ) LOCAL ;



----------------------------------GPS_QUERY_COMPARE----------------------------------
------------------------------------接口对比记录表-------------------------------------
CREATE TABLE "GPS_QUERY_COMPARE" (
                                     "ID" NUMBER(22) NOT NULL,
                                     "LOG_ID" NUMBER(22),
                                     "API_ID" VARCHAR2(20),
                                     "RESULT" VARCHAR2(2),
                                     "REQ_MSG" CLOB,
                                     "NEW_RES" CLOB,
                                     "OLD_RES" CLOB,
                                     "PUB_COMPARE_RESULT" VARCHAR2(255),
                                     "PRI_COMPARE_RESULT" VARCHAR2(2),
                                     "NEW_TRANS_NO" VARCHAR2(21),
                                     "NEW_SYS_SOURCE" VARCHAR2(6),
                                     "NEW_TRANS_DATE" VARCHAR2(30),
                                     "NEW_TRANS_TIME" VARCHAR2(24),
                                     "NEW_REPLY_CODE" VARCHAR2(21),
                                     "NEW_REPLY_MSG" VARCHAR2(255),
                                     "NEW_IDENTIFY_CODE" VARCHAR2(24),
                                     "NEW_CUR_PAGE" VARCHAR2(12),
                                     "NEW_TOTAL_PAGES" VARCHAR2(12),
                                     "NEW_PAGE_SIZE" VARCHAR2(12),
                                     "OLD_TRANS_NO" VARCHAR2(21),
                                     "OLD_SYS_SOURCE" VARCHAR2(6),
                                     "OLD_TRANS_DATE" VARCHAR2(30),
                                     "OLD_TRANS_TIME" VARCHAR2(24),
                                     "OLD_REPLY_CODE" VARCHAR2(21),
                                     "OLD_REPLY_MSG" VARCHAR2(255),
                                     "OLD_IDENTIFY_CODE" VARCHAR2(24),
                                     "OLD_CUR_PAGE" VARCHAR2(12),
                                     "OLD_TOTAL_PAGES" VARCHAR2(12),
                                     "OLD_PAGE_SIZE" VARCHAR2(12),
                                     "START_CALL_TIME" DATE,
                                     "MID_TIME" DATE,
                                     "END_CALL_TIME" DATE,
                                     "CREATE_TIME" DATE,
                                     "UPDATE_TIME" DATE,
                                     "NEW_JOLT_NODE" VARCHAR2(20),
                                     "OLD_JOLT_NODE" VARCHAR2(20),
                                     "DIFF_INDEX1" VARCHAR2(4000),
                                     "DIFF_INDEX2" VARCHAR2(4000),
                                     "DIFF_INDEX3" VARCHAR2(4000),
                                     "DIFF_INDEX4" VARCHAR2(4000),
                                     "DIFF_INDEX5" VARCHAR2(4000),
                                     "DIFF_INDEX6" VARCHAR2(4000),
                                     CONSTRAINT "PK_CP_QUERY_COMPARE" PRIMARY KEY ("ID")
);

-- 表注释
COMMENT ON TABLE GPS_QUERY_COMPARE IS '接口对比记录表，用于存储新老系统接口调用结果及差异分析信息';

-- 字段注释
COMMENT ON COLUMN GPS_QUERY_COMPARE.ID IS '主键ID（自增）';
COMMENT ON COLUMN GPS_QUERY_COMPARE.LOG_ID IS '关联的日志ID';
COMMENT ON COLUMN GPS_QUERY_COMPARE.API_ID IS '接口唯一标识';
COMMENT ON COLUMN GPS_QUERY_COMPARE.RESULT IS '对比结果（如：0-一致 1-不一致）';
COMMENT ON COLUMN GPS_QUERY_COMPARE.REQ_MSG IS '请求报文内容';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_RES IS '新系统返回报文';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_RES IS '旧系统返回报文';
COMMENT ON COLUMN GPS_QUERY_COMPARE.PUB_COMPARE_RESULT IS '公共部分对比结果描述';
COMMENT ON COLUMN GPS_QUERY_COMPARE.PRI_COMPARE_RESULT IS '私有部分对比结果（0-一致 1-不一致）';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_TRANS_NO IS '新系统交易流水号';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_SYS_SOURCE IS '新系统来源标识';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_TRANS_DATE IS '新系统交易日期';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_TRANS_TIME IS '新系统交易时间';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_REPLY_CODE IS '新系统返回码';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_REPLY_MSG IS '新系统返回消息';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_IDENTIFY_CODE IS '新系统识别码';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_CUR_PAGE IS '新系统当前页码';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_TOTAL_PAGES IS '新系统总页数';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_PAGE_SIZE IS '新系统每页大小';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_TRANS_NO IS '旧系统交易流水号';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_SYS_SOURCE IS '旧系统来源标识';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_TRANS_DATE IS '旧系统交易日期';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_TRANS_TIME IS '旧系统交易时间';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_REPLY_CODE IS '旧系统返回码';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_REPLY_MSG IS '旧系统返回消息';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_IDENTIFY_CODE IS '旧系统识别码';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_CUR_PAGE IS '旧系统当前页码';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_TOTAL_PAGES IS '旧系统总页数';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_PAGE_SIZE IS '旧系统每页大小';
COMMENT ON COLUMN GPS_QUERY_COMPARE.START_CALL_TIME IS '开始调用时间';
COMMENT ON COLUMN GPS_QUERY_COMPARE.MID_TIME IS '中间处理完成时间';
COMMENT ON COLUMN GPS_QUERY_COMPARE.END_CALL_TIME IS '调用结束时间';
COMMENT ON COLUMN GPS_QUERY_COMPARE.CREATE_TIME IS '记录创建时间';
COMMENT ON COLUMN GPS_QUERY_COMPARE.UPDATE_TIME IS '最后更新时间';
COMMENT ON COLUMN GPS_QUERY_COMPARE.NEW_JOLT_NODE IS '新系统 Jolt 服务器节点';
COMMENT ON COLUMN GPS_QUERY_COMPARE.OLD_JOLT_NODE IS '旧系统 Jolt 服务器节点';
COMMENT ON COLUMN GPS_QUERY_COMPARE.DIFF_INDEX1 IS '差异索引字段1';
COMMENT ON COLUMN GPS_QUERY_COMPARE.DIFF_INDEX2 IS '差异索引字段2';
COMMENT ON COLUMN GPS_QUERY_COMPARE.DIFF_INDEX3 IS '差异索引字段3';
COMMENT ON COLUMN GPS_QUERY_COMPARE.DIFF_INDEX4 IS '差异索引字段4';
COMMENT ON COLUMN GPS_QUERY_COMPARE.DIFF_INDEX5 IS '差异索引字段5';
COMMENT ON COLUMN GPS_QUERY_COMPARE.DIFF_INDEX6 IS '差异索引字段6';

-- 已有索引，可用于按时间、接口ID和结果查询优化
CREATE INDEX "SYSTEM"."IDX_CP_QUERY_COMPARE_1" ON "SYSTEM"."GPS_QUERY_COMPARE" ("CREATE_TIME", "API_ID", "RESULT") GLOBAL;

CREATE SEQUENCE SEQ_GPS_QUERY_COMPARE_ID
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE;

CREATE OR REPLACE TRIGGER TRG_GPS_QUERY_COMPARE_ID
    BEFORE INSERT ON GPS_QUERY_COMPARE
    FOR EACH ROW
BEGIN
    SELECT SEQ_GPS_QUERY_COMPARE_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/


------------------------------GPS_CONFIG_ENUMERATION----------------------------------------
------------------------------------配置枚举表-----------------------------------------------
CREATE TABLE "GPS_CONFIG_ENUMERATION" (
                                          "TYPE" VARCHAR2(4000) CONSTRAINT "CONFIG__TABLE_OBNOTNULL_1700123443381987" NOT NULL ENABLE,
                                          "VALUE" VARCHAR2(4000),
                                          "NOTE" VARCHAR2(120),
                                          "ONLY_BRANCH_FLAG" NUMBER
);
-- 表注释
COMMENT ON TABLE GPS_CONFIG_ENUMERATION IS '配置枚举表，用于存储系统中的枚举类型配置';

-- 字段注释
COMMENT ON COLUMN GPS_CONFIG_ENUMERATION.TYPE IS '配置类型，例如：INSERT_SIZE、PAGE_SIZE 等';
COMMENT ON COLUMN GPS_CONFIG_ENUMERATION.VALUE IS '配置类型的值，与 TYPE 对应';
COMMENT ON COLUMN GPS_CONFIG_ENUMERATION.NOTE IS '配置类型的说明或备注信息';
COMMENT ON COLUMN GPS_CONFIG_ENUMERATION.ONLY_BRANCH_FLAG IS '是否仅限于分支机构使用标志，0-否，1-是';

